<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>
    <!--引入相关文件-->
    <link rel="stylesheet" type="text/css" href="./handsontable.full.css"/>
    <script src="./handsontable.full.js"></script>
    <script src="./xlsx.full.min.js"></script>
</head>

<body>
<input type="file" onchange="impt(this)"/>
<input type="button" value="下载" onclick="down()"/>
<input type="button" value="查看结果数据数据" onclick="showData()"/>
<div id="hot"></div>
</body>
<script type="application/javascript">
    //------------------------------------------------------------------
    var hotElement = document.querySelector('#hot');//<====绑定handsontable初始化div
    var hotSettings = {//<====handsontable的配置
        autoWrapRow: true,
        manualColumnResize: true,//当值为true时，允许拖动，当为false时禁止拖动
        manualRowResize: true,//当值为true时，允许拖动，当为false时禁止拖动
        stretchH: "none",     //last:延伸最后一列,all:延伸所有列,none默认不延伸。
        manualColumnMove: true,// 当值为true时，列可拖拽移动到指定列
        manualRowMove: true,// 当值为true时，行可拖拽至指定行
        rowHeaders: true, //当值为true时显示行头，当值为数组时，行头为数组的值
        columnSorting: true,//允许排序
        sortIndicator: true,
        contextMenu: true,//显示右键菜单
        autoColumnSize: true, //当值为true且列宽未设置时，自适应列大小
        margeCells: true,
        persistentState:true,
        data: [" "],
        colHeaders: true, // 使用自定义列头
        colWidths: 150, // 设置所有列宽为150像素
        filters: true,
        undo: true,
        redo: true,
        wordWrap: true,//自动换行
        copyable: true,//是否允许键盘复制
        fillHandle: true,//是否允许拖动复制
        mergeCells: true,//合并单元格
        renderAllRows: true,//是否呈现所有行
        allowInsertRow: true,
        allowInsertColumn: true,
        customBorders: true,//自定义边框设置,可数组定义
        dropdownMenu: false,//表头筛选
    };
    var hot = new Handsontable(hotElement, hotSettings);
    //------------------------------------------------------------------
    var utl = {};
    utl.Binary = {
        fixdata(data) { //文件流转BinaryString
            var o = "",
                l = 0,
                w = 10240;
            for (; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w + w)));
            o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)));
            return o;
        },
        s2ab(s) { //字符串转字符流
            var buf = new ArrayBuffer(s.length);
            var view = new Uint8Array(buf);
            for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
            return buf;
        }
    };
    utl.XLSX = {
        wb: null,
        rABS: false,
        import(f, c) {//导入根据文件
            this.wb = null;
            var reader = new FileReader();
            reader.onload = function (e) {
                var data = e.target.result;
                if (utl.XLSX.rABS) {
                    utl.XLSX.wb = XLSX.read(btoa(utl.Binary.fixdata(data)), {//手动转化
                        type: 'base64'
                    });
                } else {
                    utl.XLSX.wb = XLSX.read(data, {
                        type: 'binary'
                    });
                }
                if (c && typeof (c)) {
                    c();
                }
            };
            if (utl.XLSX.rABS) {
                reader.readAsArrayBuffer(f);
            } else {
                reader.readAsBinaryString(f);
            }
        },
        onImport(obj, c) {//导入根据 input file标签
            if (!obj.files) {
                return;
            }
            this.import(obj.files[0], c);
        },
        getSheetsByIndex(index = 0) {//获取数据根据Sheets索引
            return XLSX.utils.sheet_to_json(this.wb.Sheets[this.wb.SheetNames[index]]);
        },
        getCharCol(n) {
            let temCol = '',
                s = '',
                m = 0
            while (n > 0) {
                m = n % 26 + 1
                s = String.fromCharCode(m + 64) + s
                n = (n - m) / 26
            }
            return s
        },
        export(json, title, type) {//导出
            var keyMap = [];//获取keys
            for (k in json[0]) {
                keyMap.push(k);
            }
            var tmpdata = [];//用来保存转换好的json
            json.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
                v: v[k],
                position: (j > 25 ? utl.XLSX.getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1)
            }))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => tmpdata[v.position] = {
                v: v.v
            });
            var outputPos = Object.keys(tmpdata); //设置区域,比如表格从A1到D10
            var tmpWB = new Object();
            title = title ? title : "mySheet";
            tmpWB.SheetNames = [title];
            tmpWB.Sheets = {};
            tmpWB.Sheets[title] = Object.assign({}, tmpdata, //内容
                {
                    '!ref': outputPos[0] + ':' + outputPos[outputPos.length - 1] //设置填充区域
                });
            return new Blob([utl.Binary.s2ab(XLSX.write(tmpWB,
                {bookType: (type == undefined ? 'xlsx' : type), bookSST: false, type: 'binary'}//这里的数据是用来定义导出的格式类型
            ))], {type: ""}); //创建二进制对象写入转换好的字节流
        },
        onExport(json, title, type) {//直接下载
            utl.Download.byObj(this.export(json, title, type), "下载.xlsx");
        }
    };
    utl.Download = {
        byURL(url, fileName) {//动态下载
            var tmpa = document.createElement("a");
            tmpa.download = fileName || "下载";
            tmpa.href = url; //绑定a标签
            tmpa.click(); //模拟点击实现下载
        },
        byObj(obj, fileName) {//内存二进制数据下载
            this.byURL(URL.createObjectURL(obj), fileName);
            setTimeout(function () { //延时释放
                URL.revokeObjectURL(obj); //用URL.revokeObjectURL()来释放这个object URL
            }, 100);
        }
    };
    utl.Object = {
        reverse(obj) {//对象键值倒转
            var o = new Object();
            for (var k in obj) {
                o[obj[k]] = k;
            }
            return o;
        },
        deepCopy() {//深拷贝
            let temp = obj.constructor === Array ? [] : {}
            for (let val in obj) {
                temp[val] = typeof obj[val] == 'object' ? deepCopy(obj[val]) : obj[val];
            }
            return temp;
        },
        copyJson(o) {
            return JSON.parse(JSON.stringify(o));
        }//拷贝JSON格式
    };

    function impt(emt) {//<===导入
        //照着文档抄的，有问题看文档
        var files = emt.files;
        var fileReader = new FileReader();
        fileReader.onload = function (ev) {
            var data = ev.target.result;
            var workbook = XLSX.read(data, {type: 'binary'});
            for (var sheet in workbook.Sheets) {
                if (workbook.Sheets.hasOwnProperty(sheet)) {
                    var data_str = XLSX.utils.sheet_to_csv(workbook.Sheets[sheet]);
                    var arr1 = data_str.split('\n');
                    var tmp = new Array();
                    for (var i = 0; i < arr1.length; i++) {
                        //这里有bug，格里边如果有","会造成分割错误
                        var arr2 = arr1[i].split(',');
                        var t = new Array();
                        for (var j = 0; j < arr2.length; j++) {
                            var ele = arr2[j].replace(/\"/g, "");
                            t[j] = ele
                        }
                        tmp[i] = t;
                    }
                    hot.loadData(tmp);
                    break;//只取第一个sheet
                    // console.log(XLSX.utils.sheet_to_json(workbook.Sheets[sheet]))
                    // console.log(XLSX.utils.sheet_to_csv(workbook.Sheets[sheet]))
                    // console.log(XLSX.utils.sheet_to_formulae(workbook.Sheets[sheet]))
                }
            }
        };
        // 以二进制方式打开文件
        fileReader.readAsBinaryString(files[0]);
        return false
        utl.XLSX.onImport(emt, function () {
            var rt = utl.XLSX.getSheetsByIndex();//<===默认获取Sheet1
            console.log(rt);
            var tmp = [];
            rt.forEach(function (value, index, array) {
                var t = [];
                var i = 0;
                for (var k in value) {
                    t[i] = value[k];
                    i++
                }
                tmp[index] = t;
            });
            console.log(tmp);
            hot.loadData(tmp);//<====将读取完成数据显示到handsontable
        });
    }

    function down() {//<===下载导出数据
        var d = utl.Object.copyJson(hot.getSourceData());//<=====读取handsontable的数据
        // d.unshift(utl.Object.reverse(xHred));//<====追加到列头
        utl.XLSX.onExport(d);//<====导出
    }

    function showData() {//<====获取真实数据
        var tmp = [];
        hot.getSourceData().forEach(function (value, index, array) {
            var t = new Object();
            for (var k in value) {
                t[k] = filterVal[k] ? filterVal[k](value[k]) : value[k];//<====将xlsx数据转为实际需要的值
            }
            tmp.push(t);
        });
        /*
        *更多规则自己定义吧
        */
        alert(JSON.stringify(tmp));
        window.open("data:text/html;charset=utf-8," + JSON.stringify(tmp));
        console.log(tmp);
        return tmp;
    }

</script>
</html>